package com.xy.sqldoc.service;

import com.xy.sqldoc.enums.ExportType;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Pattern;

@Service
@Slf4j
public class ExcelExportService extends AbstractExportService {

    /**
     * 导出数据库文档到Excel
     *
     * @param databaseName    数据库名称
     * @param tables          表信息列表
     * @param tableColumnsMap 表字段信息映射
     */
    @Override
    public void export(String databaseName,
                       List<Map<String, Object>> tables,
                       Map<String, List<Map<String, Object>>> tableColumnsMap,
                       String fileName) {

        log.info("开始导出数据库文档到Excel...");

        // 创建工作簿
        try (Workbook workbook = new XSSFWorkbook()) {
            // 创建表目录工作表
            createTableDirectorySheet(databaseName, workbook, tables);

            // 为每个表创建工作表
            for (Map<String, Object> table : tables) {
                String tableName = (String) table.get("table_name");
                String tableComment = Objects.toString(table.get("table_comment"), "");
                List<Map<String, Object>> columns = tableColumnsMap.get(tableName);

                if (columns != null && !columns.isEmpty()) {
                    createTableStructureSheet(workbook, tableName, tableComment, columns);
                }
            }

            // 写入文件
            try (FileOutputStream outputStream = new FileOutputStream(outputPath + fileName)) {
                workbook.write(outputStream);
                log.info("Excel文档导出成功，输出路径：{}", outputPath);
            } catch (IOException e) {
                log.error("写入Excel文档失败", e);
            }

        } catch (IOException e) {
            log.error("创建Excel工作簿失败", e);
        }
    }

    /**
     * 优化列宽，考虑中文显示
     *
     * @param sheet       工作表
     * @param columnCount 列数
     */
    private void autoSizeColumns(Sheet sheet, int columnCount) {
        // 中文字体宽度系数（相对于英文字符）
        final float CHINESE_FONT_WIDTH_FACTOR = 1.8f;
        // 最小列宽（字符数）
        final int MIN_COLUMN_WIDTH = 8;
        // 最大列宽（字符数）
        final int MAX_COLUMN_WIDTH = 50;

        for (int i = 0; i < columnCount; i++) {
            // 先使用POI自带的自动调整列宽功能
            sheet.autoSizeColumn(i, true); // 使用带参数的方法，确保考虑所有行

            // 获取当前列宽（单位：1/256字符宽度）
            int currentWidth = sheet.getColumnWidth(i);

            // 计算基于字符数的列宽
            int charWidth = currentWidth / 256;

            // 检查是否包含中文
            boolean hasChinese = false;
            for (Row row : sheet) {
                Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                if (cell.getCellType() == CellType.STRING) {
                    String value = cell.getStringCellValue();
                    if (Pattern.compile("[\u4e00-\u9fa5]").matcher(value).find()) {
                        hasChinese = true;
                        break;
                    }
                }
            }

            // 如果包含中文，增加列宽
            if (hasChinese) {
                charWidth = (int) (charWidth * CHINESE_FONT_WIDTH_FACTOR);
            }

            // 确保列宽在最小和最大之间
            charWidth = Math.max(charWidth, MIN_COLUMN_WIDTH);
            charWidth = Math.min(charWidth, MAX_COLUMN_WIDTH);

            // 设置调整后的列宽
            sheet.setColumnWidth(i, charWidth * 256);
        }
    }

    /**
     * 创建表目录工作表
     */
    private void createTableDirectorySheet(String databaseName, Workbook workbook, List<Map<String, Object>> tables) {
        Sheet sheet = workbook.createSheet("表目录");

        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("数据库表目录" + databaseName);

        // 设置标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 16);
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleCell.setCellStyle(titleStyle);

        // 合并标题单元格
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, 2));

        // 创建表头行
        Row headerRow = sheet.createRow(1);
        String[] headers = {"序号", "表名", "注释/说明"};

        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }

        // 设置数据行样式
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.LEFT);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);

        // 填充数据
        for (int i = 0; i < tables.size(); i++) {
            Map<String, Object> table = tables.get(i);
            Row dataRow = sheet.createRow(i + 2);

            // 序号
            Cell indexCell = dataRow.createCell(0);
            indexCell.setCellValue(i + 1);
            indexCell.setCellStyle(dataStyle);

            // 表名
            Cell nameCell = dataRow.createCell(1);
            nameCell.setCellValue((String) table.get("table_name"));
            nameCell.setCellStyle(dataStyle);

            // 注释/说明
            Cell commentCell = dataRow.createCell(2);
            commentCell.setCellValue(Objects.toString(table.get("table_comment"), ""));
            commentCell.setCellStyle(dataStyle);
        }

        // 优化调整列宽
        autoSizeColumns(sheet, headers.length);
    }

    /**
     * 创建表结构工作表
     */
    private void createTableStructureSheet(Workbook workbook, String tableName, String tableComment,
                                           List<Map<String, Object>> columns) {
        // 工作表名称不能超过31个字符
        String sheetName = tableName.length() > 31 ? tableName.substring(0, 31) : tableName;
        Sheet sheet = workbook.createSheet(sheetName);

        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(tableName + " (" + tableComment + ")");

        // 设置标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 14);
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleCell.setCellStyle(titleStyle);

        // 合并标题单元格
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, 8));

        // 创建表头行
        Row headerRow = sheet.createRow(1);
        String[] headers = {"序号", "列名", "数据类型", "长度", "主键", "自增", "允许空", "默认值", "列说明"};

        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }

        // 设置数据行样式
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.LEFT);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);

        // 填充数据
        for (int i = 0; i < columns.size(); i++) {
            Map<String, Object> column = columns.get(i);
            Row dataRow = sheet.createRow(i + 2);

            // 序号
            Cell indexCell = dataRow.createCell(0);
            indexCell.setCellValue(i + 1);
            indexCell.setCellStyle(dataStyle);

            // 列名
            Cell nameCell = dataRow.createCell(1);
            nameCell.setCellValue((String) column.get("column_name"));
            nameCell.setCellStyle(dataStyle);

            // 数据类型
            Cell typeCell = dataRow.createCell(2);
            typeCell.setCellValue((String) column.get("data_type"));
            typeCell.setCellStyle(dataStyle);

            // 长度
            Cell lengthCell = dataRow.createCell(3);
            Integer length = (Integer) column.get("character_maximum_length");
            if (length != null) {
                lengthCell.setCellValue(length);
            }
            lengthCell.setCellStyle(dataStyle);

            // 主键
            Cell keyCell = dataRow.createCell(4);
            String columnKey = (String) column.get("column_key");
            keyCell.setCellValue("PRI".equals(columnKey) ? "√" : "");
            keyCell.setCellStyle(dataStyle);

            // 自增
            Cell autoIncCell = dataRow.createCell(5);
            String isAutoIncrement = (String) column.get("is_auto_increment");
            autoIncCell.setCellValue("YES".equals(isAutoIncrement) ? "√" : "");
            autoIncCell.setCellStyle(dataStyle);

            // 允许空
            Cell nullableCell = dataRow.createCell(6);
            String isNullable = (String) column.get("is_nullable");
            nullableCell.setCellValue("YES".equals(isNullable) ? "√" : "");
            nullableCell.setCellStyle(dataStyle);

            // 默认值
            Cell defaultCell = dataRow.createCell(7);
            String columnDefault = Objects.toString(column.get("column_default"), "");
            defaultCell.setCellValue(columnDefault);
            defaultCell.setCellStyle(dataStyle);

            // 列说明
            Cell commentCell = dataRow.createCell(8);
            String columnComment = Objects.toString(column.get("column_comment"), "");
            commentCell.setCellValue(columnComment);
            commentCell.setCellStyle(dataStyle);
        }

        // 优化调整列宽
        autoSizeColumns(sheet, headers.length);
    }

    @Override
    public ExportType getExportType() {
        return ExportType.EXCEL;
    }
}